package com.jh.hts_jdk.io;

import java.io.File;
import java.io.FileOutputStream;
import java.util.HashMap;

import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;

import com.jh.hts_jdk.item.DataFrame;

public class XlsxWriter {

    public static final String FORMAT_NUMBER_COMMA = "#,##0";
    public static final String FORMAT_NUMBER_00 = "0.00";
    public static final String FORMAT_PERCENTAGE_00 = "0.00%";

    private File out_file = new File("test.out.xlsx");
    private Workbook workbook = new SXSSFWorkbook(1000);
    private DataFormat work_format = workbook.createDataFormat();
    private CellStyle header_style;
    private HashMap<String, CellStyle> style_map = new HashMap<>();

    public XlsxWriter(File out_file) {
        this();
        if (out_file != null) {
            this.out_file = out_file;
        }
    }

    public XlsxWriter() {
        header_style = workbook.createCellStyle();
        header_style.setAlignment(HorizontalAlignment.CENTER);
        for (String s : new String[] { FORMAT_NUMBER_00, FORMAT_NUMBER_COMMA, FORMAT_PERCENTAGE_00 }) {
            addStyle(s);
        }
    }

    public void writeData(Sheet sheet, DataFrame<?, ?> data, int row_index, int col_index, String[] row_format, String[] col_format) {
        writeData(sheet, data, row_index, col_index, row_format, col_format, true, true);
    }

    /**
     * 
     * @param sheet
     * @param data
     * @param row_index  数据写入的起始行，如果row_index小于0则会自动写入最后
     * @param col_index  数据写入的起始列，0为第一列
     * @param row_format 每一行的格式
     * @param col_format 每一列的格式
     * @param row_header 是否输出行名
     * @param col_header 是否输出列名
     */

    public void writeData(Sheet sheet, DataFrame<?, ?> data, int row_index, int col_index, String[] row_format, String[] col_format, boolean row_header, boolean col_header) {
        if (sheet == null || data == null) {
            throw new IllegalArgumentException("sheet or data is null!");
        }
        if (row_format != null && col_format != null) {
            throw new IllegalArgumentException("row format and col format only exist one when write data!");
        }
        CellStyle[] row_styles = null;
        CellStyle[] col_styles = null;
        if (row_format != null) {
            if (row_format.length < 1) {
                row_format = new String[1];
            }
            // 初始化行格式
            row_styles = new CellStyle[data.getRowName().length];
            for (int i = 0; i < data.getRowName().length; i++) {
                String format = row_format[i % row_format.length];
                if (format != null && !format.isEmpty()) {
                    row_styles[i] = style_map.getOrDefault(format, header_style);
                } else {
                    row_styles[i] = this.header_style;
                }
            }
        }
        if (col_format != null) {
            if (col_format.length < 1) {
                col_format = new String[1];
            }
            // 初始化列格式
            col_styles = new CellStyle[data.getColName().length];
            for (int j = 0; j < data.getColName().length; j++) {
                String format = col_format[j % col_format.length];
                if (format != null && !format.isEmpty()) {
                    col_styles[j] = style_map.getOrDefault(format, header_style);
                } else {
                    col_styles[j] = this.header_style;
                }
            }
        }
        if (row_index < 0) {
            row_index = sheet.getLastRowNum() + 1;
        }

        int col_offset = row_header ? 1 : 0;
        int row_offset = col_header ? 1 : 0;
        Cell cell;
        Row current_row = sheet.createRow(row_index);
        // 输出列名
        if (col_header) {
            cell = current_row.createCell(col_index);
            if (row_header) {
                cell.setCellValue(data.name);
                cell.setCellStyle(header_style);
            }
            for (int j = 0; j < data.getColName().length; j++) {
                cell = current_row.createCell(col_index + col_offset + j);
                cell.setCellValue(String.valueOf(data.getColName()[j]));
                cell.setCellStyle(header_style);
            }
        }
        // 输出每一行
        for (int i = 0; i < data.getRowName().length; i++) {
            current_row = sheet.createRow(row_index + row_offset + i);
            if (row_header) {
                // 输出行名
                cell = current_row.createCell(col_index);
                cell.setCellValue(String.valueOf(data.getRowName()[i]));
                cell.setCellStyle(header_style);
            }
            for (int j = 0; j < data.getColName().length; j++) {
                // 输出每行的值
                cell = current_row.createCell(col_index + col_offset + j);
                String value = String.valueOf(data.getValue(i, j));
                try {
                    cell.setCellValue(Double.valueOf(value));
                } catch (Exception e) {
                    cell.setCellValue(value);
                }
                if (row_styles != null && row_styles[i] != null) {
                    cell.setCellStyle(row_styles[i]);
                }
                if (col_styles != null && col_styles[j] != null) {
                    cell.setCellStyle(col_styles[j]);
                }
            }
        }

    }

    public void writeBlankRow(Sheet sheet, int row_index) {
        if (row_index < 0) {
            row_index = sheet.getLastRowNum() + 1;
        }
        sheet.createRow(row_index).createCell(0);
    }

    public Sheet createSheet(String name) {
        return workbook.createSheet(name);
    }

    public void addStyle(String style_str) {
        CellStyle style = workbook.createCellStyle();
        style.setAlignment(HorizontalAlignment.CENTER);
        style.setDataFormat(work_format.getFormat(style_str));
        addStyle(style_str, style);
    }

    public void addStyle(String style_id, CellStyle style) {
        style_map.put(style_id, style);
    }

    public void close() {
        try {
            FileOutputStream outputStream = new FileOutputStream(out_file);
            workbook.write(outputStream);
            workbook.close();
            outputStream.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    public void close(File file) {
        this.out_file = file;
        close();
    }

    public static void test() {
        XlsxWriter writer = new XlsxWriter();
        DataFrame<String, String> data = new DataFrame<>(new String[] { "1", "2", "3" }, new String[] { "4", "5", "6" }, 0);
        data.setValue(0, 0, 123456);
        data.setValue(0, 1, 0.98765);
        data.setValue(2, 2, 33455.6789);
        Sheet new_sheet = writer.createSheet("222");
        writer.writeData(new_sheet, data, 1, 3, null, new String[] { FORMAT_NUMBER_COMMA, FORMAT_PERCENTAGE_00, null });
        writer.close();
    }
}
